6 Relationele databases en SQL

In dit versalg voeg ik drie sets gegevens toe aan een SQL-database en maak ik drie grafieken om mijn vaardigheden in R en SQL te laten zien. Werken met databases zoals SQL is handig voor het beheren van veel gegevens.

Alle benodige libraries laden

library(tidyverse)
library(here)
library(dslabs)
library(grid)
library(readr)
library(RPostgreSQL)
library(gridExtra)
library(ggpubr)
library(stringr)
library(png)
library(DT)
library(ggplot2)

In dit chunk heb ik de alle data van github geladen en daarna heb ik alle data wel een tidy gemaakt met behulp met verschillende functies om daarna alle drie data samen met elkaar toe te voegen. En worden alle data als csv en rds opgeslagd.

## Flu data laden

flu_data <- read_csv("https://raw.githubusercontent.com/DataScienceILC/tlsc-dsfb26v-20_workflows/main/data/flu_data.csv", skip = 10)
## Rows: 659 Columns: 30
## ── Column specification ─────────────────────────────────────────────────────────────────────────
## Delimiter: ","
## dbl  (29): Argentina, Australia, Austria, Belgium, Bolivia, Brazil, Bulgaria...
## date  (1): Date
## 
## ℹ Use `spec()` to retrieve the full column specification for this data.
## ℹ Specify the column types or set `show_col_types = FALSE` to quiet this message.
## Laten we zien de inhoud van de eerste 10 regels van flu data
datatable(flu_data, options = list(scrollx=TRUE, pageLength = 10))
## Dengue data laden
dengue_data <- read_csv("https://raw.githubusercontent.com/DataScienceILC/tlsc-dsfb26v-20_workflows/main/data/dengue_data.csv", skip = 10)
## Rows: 659 Columns: 11
## ── Column specification ─────────────────────────────────────────────────────────────────────────
## Delimiter: ","
## dbl  (10): Argentina, Bolivia, Brazil, India, Indonesia, Mexico, Philippines...
## date  (1): Date
## 
## ℹ Use `spec()` to retrieve the full column specification for this data.
## ℹ Specify the column types or set `show_col_types = FALSE` to quiet this message.
## Laten we zien de inhoud van de eerste 10 regels van dengue data
datatable(dengue_data, options = list(scrollx=TRUE, pageLength = 10))
## Gapminder data van de dslabs package
gapminder_data <- gapminder

## Laten we zien de inhoud van de eerste 10 regels van gapminder data
datatable(gapminder_data, options = list(scrollx=TRUE, pageLength = 10))
#Data Tidy maken
##Flu data tidy 
flu_data_tidy <- flu_data %>% pivot_longer(cols = -Date, names_to = "country", values_to = "flu_aantal")

## Verwijder de dag en de maand van de Date kolom, hernomen van de Date kolom naar Year en aanpassen van variabelen data typen 
flu_data_tidy$Date <- str_sub(flu_data_tidy$Date, start = 1, end = 4)
flu_data_tidy <- rename(flu_data_tidy, year = Date)
flu_data_tidy$country <- as.factor(flu_data_tidy$country)
flu_data_tidy$year <- as.integer(flu_data_tidy$year)

## Laten we zien hoe ziet de data nu eruit
datatable(flu_data_tidy, options = list(scrollx=TRUE, pageLength = 10))
## Warning in instance$preRenderHook(instance): It seems your data is too big for
## client-side DataTables. You may consider server-side processing:
## https://rstudio.github.io/DT/server.html
## Dengue data tidy 
dengue_data_tidy <- dengue_data %>% pivot_longer(cols = -Date, names_to = "country", values_to = "dengue_aantal")

## Verwijder de dag en de maand van de Date kolom, hernomen van de Date kolom naar Year en aanpassen van variabelen data typen
dengue_data_tidy$Date <- str_sub(dengue_data_tidy$Date, start = 1, end = 4)
dengue_data_tidy <- rename(dengue_data_tidy, year = Date)
dengue_data_tidy$year <- as.integer(dengue_data_tidy$year)
dengue_data_tidy$country <- as.factor(dengue_data_tidy$country)

##Laten we zien hoe ziet de data nu eruit
datatable(dengue_data_tidy, options = list(scrollx=TRUE, pageLength = 10))
# Opslaan alle drie dataframes als csv en rds bestanden
## Als csv bestanden
write.csv(flu_data_tidy, file = here("data/flu.csv"), row.names = FALSE)
write.csv(dengue_data_tidy, file = here("data/dengue.csv"), row.names = FALSE)
write.csv(gapminder_data, file = here("data/gapminder_data.csv"), row.names = FALSE)

## Als rds bestanden
saveRDS(flu_data_tidy, file = here("data/flu_data.rds"))
saveRDS(dengue_data_tidy, file = here("data/dengue_data.rds"))
saveRDS(gapminder_data, file = here("data/gapminder_data.rds"))

De volgende stap is het maken van nieuwe PostgreSQL database op DBeaver met dit code : create database workflowsdb.

6.1 Maak verbinding met de database

con <- dbConnect(RPostgres::Postgres(), dbname = “workflowsdb”, host = “localhost”, port = “5432”, user = “postgres”, password = “Fatima1996@”) ### SQL-scripts voor het schrijven van tabellen naar de database vanuit dataframes

dbWriteTable(con, “flu_data_table”, flu_data_tidy, overwrite = TRUE) dbWriteTable(con, “dengue_data_table”, dengue_data_tidy, overwrite = TRUE) dbWriteTable(con, “gapminder_data_table”, gapminder_data, overwrite = TRUE)

Disconnect van de database dbDisconnect(con)

inhoud bekijken van flu_data_table select * from flu_data_table

inhoud bekijken van dengue_data_table select * from dengue_data_table

inhoud bekijken van gapminder_data_table select * from gapminder_data_table

Bij onderstande figuur, het is de opgeslaagde SQL script

sql_script <- rasterGrob(as.raster(readPNG(here("Images/saved_sqlscript.png"))))

# laat de figuur zien
grid.arrange(sql_script)

6.2 Samenvoegende table van alle drie data

De sql script van het samenvogen table van alle drie data is in onderstaande figuur

samenvogende_sqlscript <- rasterGrob(as.raster(readPNG(here("Images/samenvoegende_table.png"))))
grid.arrange(samenvogende_sqlscript)

Het laden van de samengevoegde table

samengevoegde_table_data <- read.csv(here("data/samenvoegende_table_202312111229.csv"))
## laten we de inhoud zien
datatable(samengevoegde_table_data, options = list(scrollx=TRUE, pageLength = 10))
## Warning in instance$preRenderHook(instance): It seems your data is too big for
## client-side DataTables. You may consider server-side processing:
## https://rstudio.github.io/DT/server.html

6.3 Drie statistieken met samenvogende table

In dit project heb ik gegevens samengevoegd van verschillende tabellen en beschrijvende statistieken gegenereerd. Hieronder vindt u een overzicht van mijn aanpak en de resultaten.

## Symmary statistieken
summary(samengevoegde_table_data)
##       year        country          life_expectancy infant_mortality
##  Min.   :1960   Length:165216      Min.   :13.20   Min.   :  1.50  
##  1st Qu.:2005   Class :character   1st Qu.:72.10   1st Qu.: 13.40  
##  Median :2008   Mode  :character   Median :74.50   Median : 15.40  
##  Mean   :2007                      Mean   :73.59   Mean   : 21.98  
##  3rd Qu.:2012                      3rd Qu.:75.60   3rd Qu.: 27.40  
##  Max.   :2016                      Max.   :83.90   Max.   :276.90  
##                                                    NA's   :1453    
##    fertility       population             gdp             continent        
##  Min.   :0.840   Min.   :3.124e+04   Min.   :4.040e+07   Length:165216     
##  1st Qu.:2.000   1st Qu.:1.040e+07   1st Qu.:1.910e+10   Class :character  
##  Median :2.250   Median :4.254e+07   Median :3.979e+11   Mode  :character  
##  Mean   :2.516   Mean   :8.640e+07   Mean   :4.888e+11                     
##  3rd Qu.:2.860   3rd Qu.:1.237e+08   3rd Qu.:6.999e+11                     
##  Max.   :9.220   Max.   :1.376e+09   Max.   :1.174e+13                     
##  NA's   :187     NA's   :185         NA's   :45706                         
##     region            flu_aantal      dengue_aantal  
##  Length:165216      Min.   :    0.0   Min.   :0.000  
##  Class :character   1st Qu.:  153.0   1st Qu.:0.027  
##  Mode  :character   Median :  236.0   Median :0.059  
##                     Mean   :  442.6   Mean   :0.102  
##                     3rd Qu.:  543.0   3rd Qu.:0.118  
##                     Max.   :10555.0   Max.   :1.000  
##                     NA's   :18353     NA's   :27214
## Visualisatie 1: 
africa_flu_aantal <- samengevoegde_table_data %>% select(year, country, continent, flu_aantal) %>% filter(continent == "Americas",
                  !is.na(flu_aantal))                      

africa_flu_aantal_summary <- africa_flu_aantal %>%
  group_by(country) %>% summarise(mean = mean(flu_aantal, na.rm = TRUE), stedv = sd(flu_aantal, na.rm= TRUE))

africa_flu_aantal_summary %>% ggplot(aes(x = as.factor(country), y = mean, group = country, fill = country)) +
  geom_col() +
  labs()

## Visualisatie 2:

canda_US_summary <- africa_flu_aantal %>% select(country,flu_aantal) %>% filter(country %in% c("Canada", "United States")) %>% group_by(country) %>% summarise(mean = mean(flu_aantal), stdev = sd(flu_aantal))

canda_US_summary %>% ggplot(aes(x= country, y= mean, group= country, fill = country)) +
  geom_col() +
  geom_errorbar(aes(ymin=mean-stdev, ymax=mean+stdev), width=.2) +
  labs(title ="gemiddelde flu aantal in Canada en in US" ,
       x="Country",
       y="Flu aantal gemiddeld")

## Visualisatie 3:

life_ex_flu <- samengevoegde_table_data %>% select(year,continent, life_expectancy,flu_aantal) %>% filter(continent == "Europe" ,!is.na(flu_aantal))

life_ex_flu %>% group_by(year,continent) %>%
  mutate(flu_gem = mean(flu_aantal, na.rm = TRUE)) %>%
  ggplot(aes(x= year, y = flu_gem)) +
  geom_line(aes(colour = continent))